package gbench.sandbox.data;

import org.junit.jupiter.api.Test;

import gbench.common.fs.FileSystem;
import gbench.common.fs.XlsFile.SimpleExcel;
import gbench.common.tree.LittleTree.IRecord;
import gbench.common.tree.LittleTree.SQL;
import gbench.commonApp.data.DataApp;
import gbench.commonApp.data.DataMatrixApp;

import java.util.Arrays;
import java.util.stream.*;
import java.util.LinkedList;
import java.time.LocalDateTime;
import java.util.regex.Pattern;
import gbench.common.fs.XlsFile.DColumn;

import static gbench.common.fs.XlsFile.Tuple2.*;
import static gbench.common.tree.LittleTree.*;
import static gbench.common.tree.LittleTree.IRecord.REC;
import static gbench.common.tree.LittleTree.SQL.nsql;

public class JunitGdp {
    
    @Test
    public void foo() {
        final var excel= new SimpleExcel();
        final var path = "./gdp.xlsx";
        excel.loadXlsx(FileSystem.stream(FileSystem.path(path, this.getClass())));
        final var pattern = Pattern.compile("\\(([\\,\\d]+)\\)");
        final var recs = Stream.iterate(2019, i->i-1).map(i->TUPLE2(MFT("gdp{0,number,#}",i),i)).limit(60).flatMap(tup->{
            final var mx = excel.autoDetect(tup._1());
            mx.setHeader("rankid,nation,continent,gdp,percent");
            final var mx2 = mx.flatMapColumns(col->{
                final var cc = new LinkedList<DColumn<String>>(Arrays.asList(col));
                switch(col.getName()) {
                    case "gdp":{// 对GDP列进行处理
                        cc.add(col.map(e->{
                            final var matcher = pattern.matcher(e);
                            return matcher.find()?matcher.group(1).replace(",", ""):e;
                        }).setName("gdpvalue"));
                        break;
                    }// case
                }//switch
                return  cc;
            });
            return mx2.mapByRow(IRecord::REC).map(e->e.derive("year",tup._2(),"createtime",LocalDateTime.now()));
        }).filter(e->!e.get(0).equals("null"));
        
        // 写入数据库
        final var proto = REC("#id",1,"rankid",1,"24nation","","16continent","",
            "64gdp","","gdpvalue",1l,"32percent","","year",1,"createtime",LocalDateTime.now());// 原型结构
        final var tblname = "t_gdp";
        final var sb = SQL.builder(tblname); // 狗仔entry sql 系列构建器具
        final var dataApp = new MyDataApp("db1");
        final var createsqls = sb.get(proto).createTable(true); // 生成createsqls
        final var keys = proto.aoks2rec(compose_f(e->e.str("name"),SQL::parseFieldName)).filter2("id").keys(); // 提取键名列表 // 键名
        final var s1 = JOIN( keys, "," ); // 设置键名
        final var s2 = JOIN( keys.stream().map(e -> "e." + e), "," ); // 键名
        
        System.err.println("开始数据插入！");
        timeit(()->{// 数据插入
            dataApp.withTransaction( sess -> {
                TUPLES(createsqls).filter(t->t._1()!=1).map(t->t._2()).forEach(trycatch2(sess::sqlexecute));
                final var entries = LIST(recs);
                final var mx = entries.stream().collect(DataMatrixApp.omc);
                System.out.println(mx);
                Stream.of(  nsql ( "insert delayed into " + tblname + " (" + s1 + ") values ${ foreach e in entries (" + s2 + ") }", 
                    REC( "entries", entries  )) ).forEach( trycatch2(sess::sqlexecute) );
            });// withTransaction
        });// timeit
        
        excel.close();
        System.err.println("数据插入结束!");
    }
    
    @Test
    public void bar() {
        final var dataApp = new MyDataApp("db1");
        dataApp.withTransaction(sess->{
            final var mx = sess.sql2stream("select * from t_gdp where nation='中国'").collect(DataMatrixApp.omc);
            System.out.println(mx);
        });
    }
    
    // 数据操作类
    final class MyDataApp extends DataApp {
        public MyDataApp(String dbname) { this.reload(this.configureDbName(dbname));}
        public IRecord configureDbName(final String dbname) { return 
            cfg.derive("url", MFT("jdbc:mysql://localhost:3306/{0}?serverTimezone=GMT%2B8", dbname));
        }
    }; // MyDataApp 数据数据库操作类
}
